# -*- coding: utf-8 -*-
"""
@Project: base_python
@File: import_excel_to_oracle.py
@Author: PC
@Date: 2025/06/17
@Description: 
"""
import os
import re
import pandas as pd
import oracledb
from pypinyin import lazy_pinyin

# === 配置项 ===
excel_folder = r"E:\导入oracle"
oracle_user = "ha_12345_hl"
oracle_password = "Jschrj83130911!"
oracle_dsn = "192.168.1.253:1521/jschrjpdb1"

# === 工具函数 ===

def safe_table_name(filename):
    name = os.path.splitext(os.path.basename(filename))[0]
    name = re.sub(r"\s+", "_", name)
    name = re.sub(r"[^\u4e00-\u9fa5a-zA-Z0-9_]", "_", name)

    # 中文转拼音（保留结构）
    parts = re.split(r"(_+)", name)  # 保留多个下划线为分隔符
    converted = []
    for part in parts:
        if re.search(r"[\u4e00-\u9fa5]", part):
            pinyin = "_".join(lazy_pinyin(part))
            converted.append(pinyin)
        else:
            converted.append(part.lower())

    table_name = ''.join(converted)
    table_name = re.sub(r"_+", "_", table_name).strip("_")
    return table_name[:30]


def col_to_pinyin(columns):
    result = []
    for col in columns:
        pinyin = "_".join(lazy_pinyin(str(col)))
        pinyin = re.sub(r"[^a-zA-Z0-9_]", "_", pinyin)
        pinyin = re.sub(r"_+", "_", pinyin).strip("_").lower()
        result.append(pinyin[:30])
    return result

def infer_sql_type(series):
    if pd.api.types.is_integer_dtype(series):
        return "NUMBER"
    elif pd.api.types.is_float_dtype(series):
        return "FLOAT"
    elif pd.api.types.is_datetime64_any_dtype(series):
        return "DATE"
    else:
        max_len = series.astype(str).map(len).max()
        if max_len > 3900:
            return "CLOB"
        else:
            return "VARCHAR2(4000)"

# === 主程序 ===

def import_excel_to_oracle():
    conn = oracledb.connect(user=oracle_user, password=oracle_password, dsn=oracle_dsn)
    cursor = conn.cursor()

    for file in os.listdir(excel_folder):
        if not file.lower().endswith((".xlsx", ".xls")):
            continue

        filepath = os.path.join(excel_folder, file)
        print(f"\n🚀 正在处理文件：{file}")
        
        try:
            df = pd.read_excel(filepath, dtype=str)
            if df.empty:
                print("⚠️ 空表，跳过")
                continue
            
            df = df.dropna(how='all')  # 删除空行
            df.columns = col_to_pinyin(df.columns)
            df = df.astype(str)
            table_name = safe_table_name(file)

            # 检查表是否存在
            cursor.execute("""
                SELECT COUNT(*) FROM user_tables WHERE table_name = :1
            """, (table_name.upper(),))
            exists = cursor.fetchone()[0] > 0

            if not exists:
                fields = []
                for col in df.columns:
                    dtype = infer_sql_type(df[col])
                    fields.append(f'"{col.upper()}" {dtype}')
                create_sql = f'CREATE TABLE "{table_name.upper()}" (\n  {", ".join(fields)}\n)'
                cursor.execute(create_sql)
                print(f"✅ 创建表成功：{table_name}")
            else:
                print(f"ℹ️ 表已存在：{table_name}（未覆盖）")

            # 插入数据
            cols = ', '.join(f'"{col.upper()}"' for col in df.columns)
            placeholders = ', '.join([f':{i+1}' for i in range(len(df.columns))])
            insert_sql = f'INSERT INTO "{table_name.upper()}" ({cols}) VALUES ({placeholders})'

            data = [tuple(row) for row in df.itertuples(index=False, name=None)]
            cursor.executemany(insert_sql, data)
            conn.commit()

            print(f"✅ 成功写入表 {table_name}：共 {len(df)} 条记录")

        except Exception as e:
            print(f"❌ 错误：{file} -> {e}")

    cursor.close()
    conn.close()
    print("\n🏁 所有文件处理完成")

# === 执行入口 ===
if __name__ == "__main__":
    import_excel_to_oracle()
